from global_param import _global_dict
import normal
import xlwings as xw
import time
import os


def judge_str(origin_str, special_str):
    if special_str in origin_str:
        return True
    return False


# 参数传入new，审批表直接重新导入，word替换照片
if __name__ == '__main__':
    param = normal.get_content()
    os.system('J:/py_interpreter/Scripts/python.exe F:/code_test/python/daily_report/change_file_date.py')
    car_id = 'car_id'
    tmp_path = _global_dict['root_path']
    file_index = {'approval_form': -1, 'person_info': -1, 'info_collection': -1}
    word_excel_type = ('.doc', '.docx', '.xls')
    file_list = normal.get_directed_file(tmp_path, word_excel_type)
    person_info = {'name': 'null', 'tel': 'null', 'id_num': 'null',
                   'pc': 'null'}
    for i, item in enumerate(file_list):
        if judge_str(item, '审批单'):
            file_index['approval_form'] = i
        elif judge_str(item, '防疫'):
            file_index['person_info'] = i
        elif judge_str(item, '信息统计'):
            file_index['info_collection'] = i
    print('---修改审批表内容---')
    # 读入汇总信息
    collection_path = tmp_path + file_list[file_index['info_collection']]
    approval_path = tmp_path + file_list[file_index['approval_form']]

    app = xw.App(visible=False, add_book=False)

    excel_collection = app.books.open(collection_path)
    collection_sheet = excel_collection.sheets[0]
    collection_rows = collection_sheet.range("a1").expand("down").rows.count
    persons_now = collection_rows - 1
    excel_approval = app.books.open(approval_path)
    approval_sheet = excel_approval.sheets[0]
    history_persons = 0
    start_row = 4
    special_value = approval_sheet.range(start_row, 10).value
    # 不会超过100人
    for i in range(start_row, start_row + 100):
        if approval_sheet.range(i, 10).value == special_value:
            history_persons += 1
        else:
            break
    # print('更新后', persons_now, '人数据', '上一次', history_persons, '人数据', sep='')
    # 插入新增的数据
    if param == 'new':
        if history_persons < persons_now:
            # print('尾行添加表格')
            entrance_pos = approval_sheet.range(start_row, 2).value
            entrance_reason = approval_sheet.range(start_row, 6).value
            receiver = approval_sheet.range(start_row, 7).value
            admittance = approval_sheet.range(start_row, 10).value

            for i in range(0, persons_now - history_persons):
                approval_sheet.api.Rows(start_row + history_persons).Insert()
                # 插入同时把序号加上
                approval_sheet.range(start_row + history_persons, 1).value = \
                    int(approval_sheet.range(start_row + history_persons - 1, 1).value) + \
                    persons_now - history_persons - i
                # 填充固定内容
                approval_sheet.range(start_row + history_persons, 2).value = entrance_pos
                approval_sheet.range(start_row + history_persons, 6).value = entrance_reason
                approval_sheet.range(start_row + history_persons, 7).value = receiver
                approval_sheet.range(start_row + history_persons, 10).value = admittance
            # 插入后无表格线，需要添加
            insert_range = approval_sheet.range((start_row + history_persons, 1),
                                                (start_row + persons_now, 10)).options(ndim=2)
            insert_range.api.Borders(11).LineStyle = 1

        elif history_persons == persons_now:
            print('直接提取覆盖')
        else:
            print('删除尾部再覆盖')
            for i in range(0, history_persons - persons_now):
                approval_sheet.range((start_row + persons_now, 1)).api.EntireRow.Delete()
        print('从信息总表提取数据覆盖审批表人员信息')
        names = collection_sheet.range((2, 1), (persons_now + 1, 1)).options(ndim=2).value
        approval_sheet.range(4, 3).value = names
        person_ids = collection_sheet.range((2, 7), (persons_now + 1, 7)).options(ndim=2).value
        approval_sheet.range(4, 4).value = person_ids
        tels = collection_sheet.range((2, 8), (persons_now + 1, 8)).options(ndim=2).value
        approval_sheet.range(4, 5).value = tels
    # 修改进出日期
    tomorrow = normal.get_days_before_ahead()
    approval_sheet.range((start_row, 8), (start_row + persons_now - 1, 9)).value = tomorrow
    # 修改填报日期
    today = time.strftime("%Y年%#m月%#d日", time.localtime())
    info = approval_sheet.range(2, 1).value
    year_index = info.find('年')
    day_index = info.find('日')
    info = info[:year_index - 4] + today + info[day_index + 1:]
    approval_sheet.range(2, 1).value = info
    print('审批表日期修改完成')
    print('---修改审批表内容完成---')

    # normal.delete_file(tmp_path + 'test.xls')
    # excel_approval.save(tmp_path + 'test.xls')
    excel_approval.save()
    excel_approval.close()
    excel_collection.close()
    app.kill()
